package com.woniuxy.creditsystem.systemmag.util;

import com.woniuxy.creditsystem.common.bean.DataDictionaryInfo;
import com.woniuxy.creditsystem.common.bean.ProcessModelInfo;
import com.woniuxy.creditsystem.systemmag.controller.DataDictionaryController;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.nio.channels.MulticastChannel;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 导入数据到数据库
 * @Author liaojian
 * */
public class ImportExcel {

    public static List<DataDictionaryInfo> getList(MultipartFile file) throws Exception {
        List<DataDictionaryInfo> lists = new ArrayList<>();
        //获取文件名称
        String fileName = file.getOriginalFilename();
        //1.获取文件的流对象
        InputStream is = file.getInputStream();
        //2.判断Excel的版本
        Workbook workbook = null;
        if(judegExcelEdition(fileName)){
            workbook = new XSSFWorkbook(is);
        }else {
            workbook = new HSSFWorkbook(is);
        }
        //获取第一张工作表
        Sheet sheet = workbook.getSheetAt(0);
        //循环获取数据
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            //循环获取到每一行
            Row row = sheet.getRow(i);
            DataDictionaryInfo data = new DataDictionaryInfo();
            //循环获取到每一列的数据
            data.setNumber(Long.parseLong(row.getCell(0).getStringCellValue()));;
            data.setDicType(row.getCell(1).getStringCellValue());
            data.setDicName(row.getCell(2).getStringCellValue());
            data.setSort(Long.parseLong(row.getCell(3).getStringCellValue()));
            data.setEditTime(new SimpleDateFormat("yyyy-MM-dd").parse(row.getCell(4).getStringCellValue()));
            data.setEditPerson(row.getCell(5).getStringCellValue());
            lists.add(data);
        }
        //释放资源
        workbook.close();
        return lists;
    }

    public static List<ProcessModelInfo> getProList(MultipartFile file) throws Exception {
        List<ProcessModelInfo> lists = new ArrayList<>();
        //获取文件名称
        String fileName = file.getOriginalFilename();
        //1.获取文件的流对象
        InputStream is = file.getInputStream();
        //2.判断Excel的版本
        Workbook workbook = null;
        if(judegExcelEdition(fileName)){
            workbook = new XSSFWorkbook(is);
        }else {
            workbook = new HSSFWorkbook(is);
        }
        //获取第一张工作表
        Sheet sheet = workbook.getSheetAt(0);
        //循环获取数据
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            //循环获取到每一行
            Row row = sheet.getRow(i);
            ProcessModelInfo pro = new ProcessModelInfo();
            //循环获取到每一列的数据
            pro.setNum(Long.parseLong(row.getCell(0).getStringCellValue()));;
            pro.setName(row.getCell(1).getStringCellValue());
            pro.setTag(row.getCell(2).getStringCellValue());
            pro.setType(row.getCell(3).getStringCellValue());
            pro.setCreateTime(new SimpleDateFormat("yyyy-MM-dd").parse(row.getCell(4).getStringCellValue()));
            pro.setProcessImg(row.getCell(5).getStringCellValue());
            lists.add(pro);
        }
        //释放资源
        workbook.close();
        return lists;
    }

    /**
     * 判断上传的excel文件版本（xls为2003，xlsx为2017）
     * @param fileName 文件路径
     * @return excel2007及以上版本返回true，excel2007以下版本返回false
     */
    private static boolean judegExcelEdition(String fileName){
        if (fileName.matches("^.+\\.(?i)(xls)$")){
            return false;
        }else {
            return true;
        }
    }
}
